package com.x.erp.dao;

import com.x.erp.model.Contract;
import com.x.framework.dao.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class ContractDao  extends BaseDao {

    private final static String selectContractListSql = "SELECT T1.*, "
    		+ "T2.SHOP_NAME,"
    		+ "T2.SHOP_LEGAL_NAME, "
    		+ "T3.MARKET_NAME "
    		+ "FROM ERP_CONTRACT T1, "
    		+ "ERP_SHOP T2, "
    		+ "ERP_MARKET T3, "
    		+ "ERP_MARKET T4 "
    		+ "WHERE "
    		+ "T1.SHOP_ID = T2.SHOP_ID "
    		+ "AND T1.MARKET_ID = T3.MARKET_ID "
    		+ "AND T4.MARKET_ID = ? AND T3.MARKET_CODE LIKE CONCAT(T4.MARKET_CODE, '%') ";

    public List<Contract> selectContractList(Contract contract) throws Exception {
        StringBuilder sql = new StringBuilder(selectContractListSql);
        List<Object> params = new ArrayList<Object>();
        params.add(contract.getMarketId());
	    selectSearch(sql, params, contract);
        sql.append("ORDER BY T1.CONTRACT_STATE DESC , T1.CONTRACT_ENDING_DATE  ASC ");
        return this.queryForList(sql.toString(), Contract.class, params, contract);
    }


	private final static String selectContractNumSql = "SELECT COUNT(T1.CONTRACT_ID) "
			+ "FROM ERP_CONTRACT T1, "
			+ "ERP_SHOP T2, "
			+ "ERP_MARKET T3, "
			+ "ERP_MARKET T4 "
			+ "WHERE "
			+ "T1.SHOP_ID = T2.SHOP_ID "
			+ "AND T1.MARKET_ID = T3.MARKET_ID "
			+ "AND T4.MARKET_ID = ? AND T3.MARKET_CODE LIKE CONCAT(T4.MARKET_CODE, '%') ";

	public int selectContractNum(Contract contract) throws Exception {
		StringBuilder sql = new StringBuilder(selectContractNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(contract.getMarketId());
		selectSearch(sql, params, contract);
		return this.queryForInt(sql.toString(), params);
	}



	/**
	 *
	 * @param
	 * @return
	 * @throws Exception
	 */
	public int updateContractExpireState(Contract contract) throws Exception {
		String pattern = "yyyy-MM-dd 00:00:00";
		String dateTime = this.getDate(new Date(),pattern);
		return this.execute("UPDATE ERP_CONTRACT SET CONTRACT_STATE = 0 WHERE CONTRACT_STATE = 1 AND CONTRACT_ENDING_DATE < ? ", dateTime);
	}



	private void selectSearch(StringBuilder sb, List<Object> params, Contract contract) {
		if (this.isString(contract.getContractId())) {
			sb.append("AND T1.CONTRACT_ID = ? ");
			params.add(contract.getContractId());
		}
		if (this.isNotNull(contract.getShopId())) {
			sb.append("AND T1.SHOP_ID = ? ");
			params.add(contract.getShopId());
		}
		if (this.isNotNull(contract.getContractNo())) {
			sb.append("AND T1.CONTRACT_NO = ? ");
			params.add(contract.getContractNo());
		}
		if (contract.getContractState() != null) {
			sb.append("AND T1.CONTRACT_STATE = ? ");
			params.add(contract.getContractState());
		}
		if (contract.getShop() != null && StringUtils.isNotEmpty(contract.getShop().getShopLegalName())){
			sb.append("AND T2.SHOP_LEGAL_NAME = ? ");
			params.add(contract.getShop().getShopLegalName());
		}
		if (contract.getContractVO() != null){
			if(contract.getContractVO().getBeginStartDate() != null){
				sb.append("AND T1.CONTRACT_START_DATE >= ? ");
				params.add(contract.getContractVO().getBeginStartDate());
			}
			if(contract.getContractVO().getBeginEndDate() != null){
				sb.append("AND T1.CONTRACT_START_DATE <= ? ");
				params.add(contract.getContractVO().getBeginEndDate());
			}
			if(contract.getContractVO().getOverStartDate() != null){
				sb.append("AND T1.CONTRACT_ENDING_DATE >= ? ");
				params.add(contract.getContractVO().getOverStartDate());
			}
			if(contract.getContractVO().getOverEndDate() != null){
				sb.append("AND T1.CONTRACT_ENDING_DATE <= ? ");
				params.add(contract.getContractVO().getOverEndDate());
			}
			if(contract.getContractVO().getEndTime() != null){
				sb.append("AND T1.CONTRACT_ENDING_DATE <= ? ");
				params.add(this.getDate(contract.getContractVO().getEndTime()));
			}
        }
	}
}
